using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using System;
using System.Drawing;

namespace Spread.Sparkline.Features
{
    public partial class CascadeSparkline : Spread.Common.Features.Sample.BaseSampleControl
    {
        public CascadeSparkline()
        {
            InitializeComponent();
            Name = "Cascade";
            _fpSpread.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = GrapeCity.Spreadsheet.CalcFeatures.All;
            InitCascadeSparklineWithType(_fpSpread.Sheets[0]);
            InitHorizontalSparkline(_fpSpread.Sheets[1]);
            InitVerticalSparkline(_fpSpread.Sheets[2]);
        }

        #region Private Methods
        private void InitCascadeSparklineWithType(SheetView sheetView)
        {
            var worksheet = sheetView.AsWorksheet();
            worksheet.Name = "Item Type";

            worksheet.Cells["B1"].Value = "Income Statement";
            worksheet.Cells["B1"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 12, Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single });
            sheetView.Cells["B1"].ForeColor = Color.Gray;

            worksheet.Cells["B2"].Value = "Profit after tax in mUSD";
            worksheet.Cells["B3"].Value = "2019 PL, AC";
            worksheet.Cells["K1"].Value = "Year";
            worksheet.Cells["K1"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Right;
            worksheet.Cells["K1"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 10, Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single });
            worksheet.Cells["K2"].Value = "2019";
            worksheet.Cells["K2"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Right;

            worksheet.Cells["B4:K4"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
            worksheet.Cells["B4:K4"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["B4:K4"].Font.Bold = true;
            worksheet.Cells["B4:K4"].Font.Underline = GrapeCity.Spreadsheet.UnderlineStyle.Single;

            worksheet.Columns["A"].ColumnWidth = 20;
            worksheet.Columns["B"].ColumnWidth = 320;
            worksheet.Columns["C"].ColumnWidth = 80;
            worksheet.Columns["E"].ColumnWidth = 300;
            worksheet.Columns["F"].ColumnWidth = 70;
            worksheet.Columns["G"].ColumnWidth = 80;
            worksheet.Columns["H"].ColumnWidth = 80;
            worksheet.Columns["I"].ColumnWidth = 300;

            worksheet.Rows["4:20"].RowHeight = 24;

            worksheet.SetValue(3, 0, new object[,]
            {
                { null, null, "PY", "ΔPY", "ΔPY", "ΔPY %", "AC", "ΔPL", "ΔPL", "ΔPL %", "PL"},
                { "+", "Software Revenue", 265000000, null, null, null, 278000000, null, null, null, 273000000},
                { "+", "Support Revenue", 87000000, null, null, null, 90000000, null, null, null, 87000000},
                { "+", "Consulting Revenue", 121000000, null, null, null, 128000000, null, null, null, 122000000},
                { "=", "Revenue", null, null, null, null, null, null, null, null, null},
                { "-", "COGS", 122000000, null, null, null, 128000000, null, null, null, 104000000},
                { "=", "Gross Profit", null, null, null, null, null, null, null, null, null},
                { "-", "Research and development expenses", 78000000, null, null, null, 91000000, null, null, null, 96000000},
                { "-", "Selling and general adminstrative expenses", 97000000, null, null, null, 102000000, null, null, null, 89000000},
                { "-", "Other operating expenses", 19000000, null, null, null, 10000000, null, null, null, 14000000},
                { "+", "Other financial income, net", 34000000, null, null, null, 30000000, null, null, null, 28000000},
                { "+", "Other operation income", 33000000, null, null, null, 27000000, null, null, null, 10000000},
                { "=", "Income from continuing operations before tax", null, null, null, null, null, null, null, null, null},
                { "-", "Income tax expenses", 23000000, null, null, null, 27000000, null, null, null, 31000000},
                { "=", "Income from continuing operations", null, null, null, null, null, null, null, null, null},
                { "+", "Income from discontinued operations", 6000000, null, null, null, 25000000, null, null, null, 20000000},
                { "=", "Net Income", null, null, null, null, null, null, null, null, null},
            });

            worksheet.Rows["8"].Font.Bold = true;
            worksheet.Rows["10"].Font.Bold = true;
            worksheet.Rows["16"].Font.Bold = true;
            worksheet.Rows["18"].Font.Bold = true;
            worksheet.Rows["20"].Font.Bold = true;

            worksheet.Cells["C8"].Formula = "SUM(C5:C7)";
            worksheet.Cells["C10"].Formula = "C8-C9";
            worksheet.Cells["C16"].Formula = "C10-C11-C12-C13+C14+C15";
            worksheet.Cells["C18"].Formula = "C16-C17";
            worksheet.Cells["C20"].Formula = "C18+C19";

            worksheet.Cells["G8"].Formula = "SUM(G5:G7)";
            worksheet.Cells["G10"].Formula ="G8-G9";
            worksheet.Cells["G16"].Formula = "G10-G11-G12-G13+G14+G15";
            worksheet.Cells["G18"].Formula = "G16-G17";
            worksheet.Cells["G20"].Formula = "G18+G19";

            worksheet.Cells["K8"].Formula = "SUM(K5:K7)";
            worksheet.Cells["K10"].Formula = "K8-K9";
            worksheet.Cells["K16"].Formula = "K10-K11-K12-K13+K14+K15";
            worksheet.Cells["K18"].Formula = "K16-K17";
            worksheet.Cells["K20"].Formula = "K18+K19";

            string numberFormat = "#,##0,,\"M\"";
            worksheet.Range("C5:C20").NumberFormat = numberFormat;
            worksheet.Range("G5:G20").NumberFormat = numberFormat;
            worksheet.Range("D5:D20").NumberFormat = numberFormat;
            worksheet.Range("H5:H20").NumberFormat = numberFormat;
            worksheet.Range("K5:K20").NumberFormat = numberFormat;

            for (int row = 5; row <= 20; row++)
            {
                worksheet.Cells[$"D{row}"].Formula = $"G{row}-C{row}";
                worksheet.Cells[$"F{row}"].Formula = $"(G{row}-C{row})/C{row}*100";
                worksheet.Cells[$"H{row}"].Formula = $"G{row}-K{row}";
                worksheet.Cells[$"J{row}"].Formula =$"(G{row}-K{row})/K{row}*100";
            }

            worksheet.Cells["F5:F20"].CellType = new NumberCellType()
            {
                DecimalPlaces = 0,
                NegativeRed = true
            };

            worksheet.Cells["J5:J20"].CellType = new NumberCellType()
            {
                DecimalPlaces = 0,
                NegativeRed = true
            };

            worksheet.Cells["P5"].Formula2 = "TEXT(D5:D20,\"#,##0,,\")& \"M\"";
            worksheet.Cells["Q5"].Formula2 = "TEXT(H5:H20,\"#,##0,,\")& \"M\"";

            worksheet.Cells["E5"].Formula2 = "CASCADESPARKLINE(D5:D20,,P5:P20,,,\"#8CBF64\",\"#D6604D\",false,A5:A20)";
            worksheet.Cells["I5"].Formula2 = "CASCADESPARKLINE(H5:H20,,Q5:Q20,,,\"#8CBF64\",\"#D6604D\",false,A5:A20)";

            //hide auxiliary columns 
            worksheet.Columns["D"].Hidden = true;
            worksheet.Columns["H"].Hidden = true;
            worksheet.Columns["P"].Hidden = true;
            worksheet.Columns["Q"].Hidden = true;
        }

        private void InitHorizontalSparkline(SheetView sheetView)
        {
            var worksheet = sheetView.AsWorksheet();
            worksheet.Name = "Horizontal";

            worksheet.Cells["A1:E1"].Merge();
            worksheet.Rows["1"].RowHeight = 30;
            worksheet.Cells["A1"].Value = "Budget Expense";
            worksheet.Cells["A1"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["A1"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 12, Bold = true });
            sheetView.Cells["A1"].BackColor = Color.Gray;
            sheetView.Cells["A1"].ForeColor = Color.White;

            worksheet.Columns["A"].ColumnWidth = 100;
            worksheet.Columns["B"].ColumnWidth = 170;
            worksheet.Columns["C"].ColumnWidth = 80;
            worksheet.Columns["D"].ColumnWidth = 80;
            worksheet.Columns["E"].ColumnWidth = 400;
            worksheet.Rows["2:15"].RowHeight = 24;

            worksheet.SetValue(1, 0, new object[,]
            {
                { null, "Opening Balance", 950, "Σ" },
                { "11/1/2020", "Bank Interest", 20, null },
                { "11/4/2020", "Rent", -390, null },
                { "11/4/2020", "Utilities", -195, null },
                { "11/5/2020", "Insurance", -87, null },
                { "11/10/2020", "Salary", 2000, null },
                { "11/11/2020", "Medical Expenses", -200, null },
                { "11/11/2020", "Groceries", -850, null },
                { "11/15/2020", "Cell Phones", -15, null },
                { "11/16/2020", "Savings", 120, null },
                { "11/19/2020", "Entertaimant", -158, null },
                { "11/20/2020", "Gas", -50, null },
                { "11/25/2020", "Luxuries", -100, null },
                { null, "Closing Balance", null, null },
            });

            worksheet.Cells["C15"].Formula = "D14";
            worksheet.Cells["D3"].Formula = "C2+C3";

            for(int row = 3; row <= 13; row++)
            {
                worksheet.Cells[$"D{row + 1}"].Formula = $"D{row}+C{row + 1}";
            }

            var currencyCellType = new CurrencyCellType()
            {
                CurrencySymbol = "$",
                PositiveFormat = CurrencyPositiveFormat.CurrencySymbolAfterWithSpace,
                NegativeFormat = CurrencyNegativeFormat.SignBeforeSpaceSymbolAfter,
                DecimalPlaces = 0
            };

            worksheet.Columns["C"].CellType = currencyCellType;
            worksheet.Cells["D3:D14"].CellType = currencyCellType;
            worksheet.Cells["A3:A14"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
            worksheet.Cells["A3:A14"].CellType = new DateTimeCellType()
            {
                DateTimeFormat = DateTimeFormat.UserDefined,
                UserDefinedFormat = "yyyy-MM-dd"
            };

            worksheet.Cells["D2"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Center;
            worksheet.Cells["D2"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["A2:E2"].Borders.ApplyBorder(new GrapeCity.Spreadsheet.Border()
            {
                Bottom = new GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            });

            worksheet.Cells["A14:E14"].Borders.ApplyBorder(new GrapeCity.Spreadsheet.Border()
            {
                Bottom = new GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            });

            worksheet.Cells["B2:D2"].Font.Bold = true;
            worksheet.Cells["B2:D2"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["B15:C15"].Font.Bold = true;
            worksheet.Cells["B15:C15"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;

            //cascade sparkline located at column E
            for (int i = 2; i <= 15; i++)
            {
                worksheet.Cells[$"E{i}"].Formula2 = "CASCADESPARKLINE($C$2:$C$15,ROW()-1,$B$2:$B$15,,,\"#8CBF64\",\"#D6604D\",false)";
            }
        }

        private void InitVerticalSparkline(SheetView sheetView)
        {
            var worksheet = sheetView.AsWorksheet();
            worksheet.Name = "Vertical";

            worksheet.Cells["A1:E1"].Merge();
            worksheet.Rows["1"].RowHeight = 30;
            worksheet.Cells["A1"].Value = "Inventory Audit";
            worksheet.Cells["A1"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["A1"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 12, Bold = true });
            sheetView.Cells["A1"].BackColor = Color.Gray;
            sheetView.Cells["A1"].ForeColor = Color.White;

            worksheet.SetValue(1, 0, new object[,]
            {
                {"Stock", "Damaged", "Refurbished", "Returns", "Salable Units" },
                {790, -220, 170, 83, 823 }
            });

            worksheet.Rows["2"].RowHeight = 24;
            worksheet.Rows["3"].RowHeight = 24;
            worksheet.Rows["4"].RowHeight = 300;
            worksheet.Rows["3"].HorizontalAlignment = GrapeCity.Spreadsheet.HorizontalAlignment.Left;

            worksheet.Cells["A2:A3"].Font.Bold = true;
            worksheet.Cells["A2:A4"].Borders.ApplyBorder(new GrapeCity.Spreadsheet.Border()
            {
                Right = new GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            });
            worksheet.Cells["E2:E3"].Font.Bold = true;
            worksheet.Cells["E2:E4"].Borders.ApplyBorder(new GrapeCity.Spreadsheet.Border() 
            {
                Left = new GrapeCity.Spreadsheet.BorderLine(GrapeCity.Spreadsheet.BorderLineStyle.Thin, GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Black))
            });

            //cascade sparkline located at 4th row
            for (int c = 65; c < 70; c++)
            {
                char col = Convert.ToChar(c);
                worksheet.Columns[$"{col}"].ColumnWidth = 100;
                worksheet.Cells[$"{col}4"].Formula2 = "CASCADESPARKLINE($A$3:$E$3,COLUMN(),$A$2:$E$2,,,\"#8CBF64\",\"#D6604D\",true)";
            }
        }
        #endregion
    }
}


//
//CascadeSparkline.Designer.cs
//



namespace Spread.Sparkline.Features
{
    partial class CascadeSparkline
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(CascadeSparkline));
            this._fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, ((object)(resources.GetObject("pnlMain.Controls"))));
            this._fpSpread_Sheet1 = this._fpSpread.GetSheet(0);
            this._fpSpread_Sheet2 = this._fpSpread.GetSheet(1);
            this._fpSpread_Sheet3 = this._fpSpread.GetSheet(2);
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this._fpSpread);
            // 
            // _fpSpread
            // 
            this._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this._fpSpread.Font = new System.Drawing.Font("Calibri", 11F);
            this._fpSpread.Location = new System.Drawing.Point(0, 0);
            this._fpSpread.Name = "_fpSpread";
            this._fpSpread.Size = new System.Drawing.Size(477, 487);
            this._fpSpread.TabIndex = 0;
            // 
            // CascadeSparkline
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "CascadeSparkline";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread _fpSpread;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet1;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet2;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet3;
    }
}
